/**
 * 
 */
package com.aptech.restrant.Bean;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.Vector;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.servlet.jsp.jstl.sql.Result;
import javax.servlet.jsp.jstl.sql.ResultSupport;
import javax.sql.DataSource;

/**
 * @author Administrator
 * 
 */
public class DBConnection {
	
	
	
	
	
	
	

	private static final String DATASOURCE = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; // 数据库源名
	private static final String DRIVER_CLASS = "jdbc:sqlserver://localhost:1433;databasename="; 
	// SQL Server JDBC 驱动类
	private static final String DATABASE_URL = "fxms"; // 数据库连接URL
	private static final String DATABASE_USER = "sa"; // 用户名
	private static final String DATABASE_PASSWORD = "sa"; // 密码
	 
	private static Connection conn = null;
	private static PreparedStatement pstmt = null;
	private static ResultSet rs = null;

	/**
	 * 
	 */
	public DBConnection() {
		
	}

	/**
	 * 获取链接
	 * 
	 * @return 链接
	 */
	private static synchronized Connection getConnection() {
		/*
		 * try { Class.forName(DATASOURCE); } catch (ClassNotFoundException e) {
		 * System.out.println("数据库加载驱动失败"); e.printStackTrace(); } try { conn =
		 * DriverManager.getConnection(DRIVER_CLASS+DATABASE_URL,DATABASE_USER,DATABASE_PASSWORD); }
		 * catch (SQLException e) { System.out.println("数据库登陆失败");
		 * e.printStackTrace(); }
		 */

		// 使用JNDI读取Tomcat安装目录/conf/context.xml文件中的数据源
		
		try {
			Context ic = new InitialContext();
			DataSource source = (DataSource) ic
					.lookup("java:comp/env/jdbc/restrant");
			// 通过JNDI读取的数据源建立数据库链接
			conn = source.getConnection();
		} catch (Exception e) {
			System.out.println("数据库登陆失败");
			e.printStackTrace();
		}
		return conn;
	}

	/**
	 * 获取链接
	 * 
	 * @return 链接
	 */
	private static synchronized Connection getConnection(String config) {
		Properties p = new Properties();
		try {
			p.load(DBConnection.class.getResourceAsStream("/" + config));
		} catch (Exception e) {
			System.out.println("读取数据配置文件失败");
		}
		try {
			Class.forName(p.getProperty("DATASOURCE"));
			conn = DriverManager.getConnection(p.getProperty("DRIVER_CLASS"), p
					.getProperty("DATABASE_USER"), p
					.getProperty("DATABASE_PASSWORD"));
		} catch (Exception e) {
			System.out.println("数据库登陆失败");
			e.printStackTrace();
		}
		return conn;
	}

	/**
	 * 
	 * executeUpdate()方法
	 * 
	 * @param sql
	 *            Sql语句
	 * @param pstmt
	 *            参数列表
	 * @return 影响的行数
	 */
	public static synchronized int executeUpdate(String sql, Object[] obj) {
		int row = 0;
		try {
			//conn = DBConnection.getConnection("db_config.properties");
			conn = DBConnection.getConnection();
			pstmt = conn.prepareStatement(sql);
			for (int i = 1; i <= pstmt.getParameterMetaData()
					.getParameterCount(); i++) {
				pstmt.setObject(i, obj[i - 1]);
			}
			row = pstmt.executeUpdate();
		} catch (Exception e) {
			System.out.println(sql + "时 DBConnection中的executeUpdate方法出错！");
			e.printStackTrace();
		} finally {
			DBConnection.close_All(conn, pstmt, null);
		}
		return row;
	}

	/**
	 * 根据Sql语句返回一个结果集
	 * 
	 * @param sql
	 *            语句
	 * @return 结果集
	 */
	public static synchronized ResultSet getResultSet(String sql) {
		try {
			//conn = DBConnection.getConnection("db_config.properties");
			conn = DBConnection.getConnection();
			pstmt = conn.prepareStatement(sql);

			rs = pstmt.executeQuery();
		} catch (Exception e) {
			//System.out.println(sql + "时 DBConnection中的getResultSet方法出错！");
			//e.printStackTrace();
		} finally {
			DBConnection.close_All(null, null, null);
		}
		return rs;
	}

	/**
	 * 根据Sql语句返回一个结果集
	 * 
	 * @param sql
	 *            语句
	 * @param str
	 *            参数列表
	 * @return 结果集
	 */
	public static synchronized ResultSet getResultSet(String sql, String[] str) {
		try {
			//conn = DBConnection.getConnection("db_config.properties");
			conn = DBConnection.getConnection();
			pstmt = conn.prepareStatement(sql);
			for (int i = 1; i <= pstmt.getParameterMetaData()
					.getParameterCount(); i++) {
				pstmt.setObject(i, str[i - 1]);
			}

			rs = pstmt.executeQuery();
		} catch (Exception e) {
			System.out.println(sql + "时 DBConnection中的getResultSet方法出错！");
			e.printStackTrace();
		} finally {
			DBConnection.close_All(null, null, null);
		}
		return rs;
	}

	/**
	 * 通用查询方法
	 * 
	 * @return
	 * @throws Exception
	 */
	public static synchronized Result executeQuery(String sql,List values) throws Exception {
		Result result = null;
    	try {
    		conn = DBConnection.getConnection();
    		
    		//使用预处理语句，并设定所有的SQL语句的所有参数值
			pstmt = conn.prepareStatement(sql);
			
			if(values!=null&&values.size()>0){
				// 循环，将SQL语句参数列表中的值依次赋给预执行语句
				for (int i = 0; i < values.size(); i++) {
					Object o = values.get(i);
					// 注意，setObject()方法的索引值从1开始，所有i+1
					pstmt.setObject(i + 1, o);
				}	
			}
			
			//执行查询SQL语句，返回查询结果集
			rs = pstmt.executeQuery();
			
			//把ResultSet转换为Result
			result = ResultSupport.toResult(rs);
			
		} catch (Exception e) {
			System.out.println(sql+"时 executeQuery 方法出错");
			//e.printStackTrace();
		}finally{
			DBConnection.close_All(conn, pstmt, rs);
		}
		return result;
    }

	
	public static synchronized ResultSet executeQuery(String sql) throws Exception {
		try {
			//conn = DBConnection.getConnection("db_config.properties");
			conn = DBConnection.getConnection();
			pstmt = conn.prepareStatement(sql);

			rs = pstmt.executeQuery();
		} catch (Exception e) {
			System.out.println(sql + "时 DBConnection中的getResultSet方法出错！");
			e.printStackTrace();
		}  
		return rs;
	}	
		
		
		
	
	
	
/*	*//**
	 * 将SQL语句参数列表中的值赋给预执行语句
	 * 
	 * @param pstmt
	 * @param values
	 *//*
	private static synchronized void setValues(PreparedStatement pstmt, List values)
			throws Exception {
		// 循环，将SQL语句参数列表中的值依次赋给预执行语句
		for (int i = 0; i < values.size(); i++) {
			Object o = values.get(i);
			// 注意，setObject()方法的索引值从1开始，所有i+1
			pstmt.setObject(i + 1, o);
		}
	}*/

	/**
	 * 关闭资源
	 * 
	 * @param conn
	 *            连接
	 * @param pstmt
	 *            参数列表
	 * @param rs
	 *            结果集
	 */
	public static synchronized void close_All(Connection conn, PreparedStatement pstmt,
			ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
				rs = null;
			} catch (SQLException e) {
				System.out.println("关闭结果集时出错");
				e.printStackTrace();
			}
		}
		if (pstmt != null) {
			try {
				pstmt.close();
				pstmt = null;
			} catch (SQLException e) {
				System.out.println("关闭参数列表时出错");
				e.printStackTrace();
			}
		}
		try {
			if (conn != null && (!conn.isClosed())) {
				conn.close();
				conn = null;
			}
		} catch (SQLException e1) {
			System.out.println("关闭链接时出错");
			e1.printStackTrace();
		}
	}

	/**
	 * 关闭资源
	 * 
	 * @param conn
	 *            连接
	 * @param pstmt
	 *            参数列表
	 * @param rs
	 *            结果集
	 */
	public static synchronized void close_All(ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
				rs = null;
			} catch (SQLException e) {
				System.out.println("关闭结果集时出错");
				e.printStackTrace();
			}
		}
		if (pstmt != null) {
			try {
				pstmt.close();
				pstmt = null;
			} catch (SQLException e) {
				System.out.println("关闭参数列表时出错");
				e.printStackTrace();
			}
		}
		try {
			if (conn != null && (!conn.isClosed())) {
				conn.close();
				conn = null;
			}
		} catch (SQLException e1) {
			System.out.println("关闭链接时出错");
			e1.printStackTrace();
		}
	}
	

	
}
